from flask import Flask, jsonify
from flask_cors import CORS
import mysql.connector

app = Flask(__name__)
CORS(app)  # 这将允许跨源请求，确保前端可以与后端通信

# 数据库连接配置
config = {
    'user': 'root',
    'password': '123456',
    'host': '127.0.0.1',
    'database': 'soft',
    'raise_on_warnings': True
}

@app.route('/api/demo')
def get_demo():
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()
    cursor.execute("SELECT id, name, sex, age, 数学, 物理, 化学 FROM demo")
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    # 将查询结果转换为字典列表
    columns = ['id', 'name', 'sex', 'age', 'math', 'physics', 'chemistry']
    data = [dict(zip(columns, row)) for row in result]
    return jsonify(data)



@app.route('/api/score-statistics')
def get_score_statistics():
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor()

    # 查询各科目成绩统计数据
    statistics = {}
    for subject in ['数学', '物理', '化学']:
        cursor.execute(f"""
            SELECT FLOOR({subject} / 10) * 10 AS score_group, COUNT(*) AS count
            FROM demo
            GROUP BY FLOOR({subject} / 10)
            ORDER BY score_group;
        """)
        result = cursor.fetchall()
        # 将查询结果转换为字典列表
        statistics[subject] = [{'score_group': row[0], 'count': row[1]} for row in result]

    cursor.close()
    conn.close()
    return jsonify(statistics)


if __name__ == '__main__':
    app.run(debug=True)
